﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;

namespace DbAccess
{
    public abstract class SimpleDataBase
    {
        protected readonly IDataBase _dataBase;

        public SimpleDataBase(IDataBase dataBase)
        {
            _dataBase = dataBase;
        }

        // 摘要:
        //     是否存在自增
        protected string IdentityForField = "";

        /// <summary>
        /// 打开数据库链接
        /// </summary>
        /// <returns></returns>
        protected internal DbConnection GetDbConnection()
        {
            var conn = _dataBase.DbConnection();

            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }

            return conn;
        }

        public ExecuteResult ExecuteResult(string cmdText, params DbParameter[] paramters)
        {
            if (_dataBase.DbType == DbBaseType.MySql)
                cmdText += ";SELECT LAST_INSERT_ID();";
            else if (_dataBase.DbType == DbBaseType.PostgreSql)
                cmdText = IdentityForField.IsNullOrEmpty() ? cmdText : (cmdText + " RETURNING " + IdentityForField + ";");
            else if (_dataBase.DbType == DbBaseType.SqlServer)
                cmdText += ";SELECT @@identity;";
            else if (_dataBase.DbType == DbBaseType.Sqlite)
                cmdText += ";SELECT last_insert_rowid();";

            using (DbConnection con = GetDbConnection())
            {
                using (DbCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = cmdText;
                    if (paramters != null && paramters.Length > 0)
                        cmd.Parameters.AddRange(paramters);
                    var reader = cmd.ExecuteReader();
                    ExecuteResult result = new ExecuteResult()
                    {
                        Success = false,
                        LastId = 0L
                    };
                    int execResult = reader.RecordsAffected;
                    if (reader.Read())
                        result.LastId = reader.GetInt64Ex(0);
                    result.Success = execResult != 0;
                    reader.Close();
                    return result;
                }
            }
        }

        public object ExecuteScalar(string cmdText, params DbParameter[] parameters)
        {
            using (DbConnection con = GetDbConnection())
            {
                using (DbCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = cmdText;
                    if (parameters != null && parameters.Length > 0)
                        cmd.Parameters.AddRange(parameters);

                    return cmd.ExecuteScalar();
                }
            }
        }
        private string GenerateBasicSelectQuery(string tableName)
           => $"SELECT * FROM {EscapeTableName(tableName)} WHERE 1=0";
        private string EscapeTableName(string tableName)
        {
            if (tableName.Contains("."))
            {
                var parts = tableName.Split('.');
                return $"[{parts[0]}].[{parts[1]}]";
            }
            return $"[{tableName}]";
        }
        public string GetDeleteSql(string tableName) => GetSql(tableName, cb => cb.GetDeleteCommand(true), out _);
        public string GetDeleteSql(string tableName, Dictionary<string, object> dic, out DbParameter[] dbParameters)
        => GetSql(tableName, cb => cb.GetDeleteCommand(true), out dbParameters, dic: dic);
        public string GetUpdateSql(string tableName) => GetUpdateSql(tableName, null, out _);
        public string GetUpdateSql(string tableName, Dictionary<string, object> dic, out DbParameter[] dbParameters)
        => GetSql(tableName, cb => cb.GetUpdateCommand(true), out dbParameters, dic: dic);
        public string GetUpdateSql(string tableName, object data, out DbParameter[] dbParameters)
        // data 暂时只支持字典、实体类、匿名类
        => GetUpdateSql(tableName, data.ConvertToDictionary(), out dbParameters);
        public string GetInsertSql(string tableName) => GetInsertSql(tableName, null, out _);
        public string GetInsertSql(string tableName, Dictionary<string, object> dic, out DbParameter[] dbParameters)
        => GetSql(tableName, cb => cb.GetInsertCommand(true), out dbParameters, dic: dic, prefix: string.Empty);
        public string GetInsertSql(string tableName, object data, out DbParameter[] dbParameters)
        // data 暂时只支持字典、实体类、匿名类
        => GetInsertSql(tableName, data.ConvertToDictionary(), out dbParameters);
        public string GetSql(string tableName, Func<DbCommandBuilder, DbCommand> func, out DbParameter[] dbParameters, ConflictOption conflictOption = ConflictOption.OverwriteChanges, Dictionary<string, object> dic = null, string prefix = "Original_")
        {
            using (DbConnection con = GetDbConnection())
            {
                DbDataAdapter adapter = _dataBase.GetFactory(con).CreateDataAdapter();
                adapter.SelectCommand = con.CreateCommand();
                dbParameters = null;
                if (dic != null)
                {
                    dbParameters = new DbParameter[dic.Count];
                    int i = 0;
                    foreach (var dicItem in dic)
                    {
                        var param = adapter.SelectCommand.CreateParameter();
                        param.ParameterName = $"@{prefix}{dicItem.Key}";
                        param.Value = dicItem.Value ?? DBNull.Value;
                        dbParameters[i++] = param;
                    }
                }
                adapter.SelectCommand.CommandText = GenerateBasicSelectQuery(tableName);
                DbCommandBuilder cb = _dataBase.GetFactory(con).CreateCommandBuilder();
                cb.ConflictOption = conflictOption;
                cb.DataAdapter = adapter;
                string sql = func(cb).CommandText;
                cb.Dispose();
                adapter.Dispose();
                return sql;
            }
        }

        /// <summary>
        /// 单个查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql">sql语句</param>
        /// <param name="reader">数据读取器</param>
        /// <param name="cmdParms">参数</param>
        /// <returns></returns>
        /// <ArgumentNullException cref="ArgumentNullException"></ArgumentNullException>
        public T QueryFirstOrDefault<T>(string sql, Func<IDataReader, T> reader, params DbParameter[] cmdParms)
        {
            if (reader == null)
            {
                throw new ArgumentNullException("数据读取器是空的!");
            }

            var model = default(T);
            using (DbConnection connection = GetDbConnection())
            {
                using (DbCommand cmd = connection.CreateCommand())
                {
                    PrepareCommand(cmd, connection, null, sql, cmdParms);
                    DbDataReader myReader = cmd.ExecuteReader();
                    cmd.Parameters.Clear();
                    if (myReader.Read())
                        model = reader(myReader);

                    myReader.Close();
                }
            }

            return model;
        }

        public DataTable Query(string cmdText, params DbParameter[] parameters)
        {
            using (DbConnection con = GetDbConnection())
            {
                DbProviderFactory factory = _dataBase.GetFactory(con);
                using (DbCommand cmd = con.CreateCommand())
                {
                    DataTable dt = new DataTable();
                    cmd.CommandText = cmdText;
                    if (parameters != null && parameters.Length > 0)
                        cmd.Parameters.AddRange(parameters);
                    DbDataAdapter da = factory.CreateDataAdapter();
                    da.SelectCommand = cmd;
                    da.Fill(dt);
                    da.Dispose();
                    return dt;
                }
            }
        }

        /// <summary>
        /// 批量查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql">sql语句</param>
        /// <param name="reader">数据读取器</param>
        /// <param name="cmdParms">参数</param>
        /// <returns></returns>
        /// <ArgumentNullException cref="ArgumentNullException"></ArgumentNullException>
        public List<T> Query<T>(string sql, Func<IDataReader, T> reader, params DbParameter[] cmdParms)
        {
            if (reader == null)
                throw new ArgumentNullException("数据读取器是空的!");

            List<T> list = new List<T>();
            using (DbConnection connection = GetDbConnection())
            {
                using (DbCommand cmd = connection.CreateCommand())
                {
                    PrepareCommand(cmd, connection, null, sql, cmdParms);
                    DbDataReader myReader = cmd.ExecuteReader();
                    cmd.Parameters.Clear();
                    while (myReader.Read())
                    {
                        list.Add(reader(myReader));
                    }
                    myReader.Close();
                }
            }
            return list;
        }

        /// <summary>
        /// 分页列表
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="tablename">表名(可以自定)</param>
        /// <param name="page">分页信息</param>
        /// <param name="where">条件</param>
        /// <param name="field">字段</param>
        /// <param name="order">排序</param>
        /// <param name="commandTimeout">超时</param>
        /// <param name="commandType">操作类型</param>
        /// <returns></returns>
        protected internal DataTable QueryWithPage(string tablename, PageInfo page, string where = "", string field = "*", string order = "")
        {
            long offset = page.Index * page.PageSize;
            string sql = "SELECT " + field + " FROM " + tablename;
            sql = ListPageSql(sql, where, order);
            sql = sql + " " + Limit(offset, page.PageSize);
            string sql2 = "SELECT COUNT(0) FROM " + tablename;
            sql2 = ListPageSql(sql2, where, "");
            //string sql3 = sql + ";" + sql2;

            DataTable dataTable = Query(sql);
            page.Count = QueryFirstOrDefault(sql2, reader => reader.GetInt64Ex(0));
            return dataTable;
        }

        public List<T> QueryWithPage<T>(string tablename, PageInfo page, Func<IDataReader, T> reader, string where = "", string field = "*", string order = "", params DbParameter[] cmdParms)
        {
            long offset = page.Index * page.PageSize;
            string sql = "SELECT " + field + " FROM " + tablename;
            sql = ListPageSql(sql, where, order);
            sql = sql + " " + Limit(offset, page.PageSize);
            string sql2 = "SELECT COUNT(0) FROM " + tablename;
            sql2 = ListPageSql(sql2, where, "");
            string sql3 = sql + ";" + sql2;
            List<T> list = new List<T>((int)page.PageSize);
            using (DbConnection conn = GetDbConnection())
            {
                using (DbCommand cmd = conn.CreateCommand())
                {
                    PrepareCommand(cmd, conn, null, sql3, cmdParms);
                    DbDataReader myReader = cmd.ExecuteReader();
                    cmd.Parameters.Clear();
                    while (myReader.Read())
                    {
                        list.Add(reader(myReader));
                    }

                    if (myReader.NextResult() && myReader.Read())
                        page.Count = myReader.GetInt64Ex(0);

                    myReader.Close();
                }
            }

            return list;
        }

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns></returns>
        public DataSet RunProcedure(string storedProcName, DbParameter[] parameters)
        {
            using (DbConnection connection = GetDbConnection())
            {
                DataSet dataSet = new DataSet();
                connection.Open();
                DbDataAdapter sqlDA = _dataBase.GetFactory(connection).CreateDataAdapter();
                sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
                sqlDA.Fill(dataSet, "ds");
                sqlDA.SelectCommand.Dispose();
                sqlDA.Dispose();
                return dataSet;
            }
        }

        /// <summary>
        /// 执行存储过程，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>SqlDataReader</returns>
        public DbDataReader RunProcedureToReader(string storedProcName, DbParameter[] parameters)
        {
            using (DbConnection connection = GetDbConnection())
            {
                DbDataReader returnReader;
                connection.Open();
                DbCommand command = BuildQueryCommand(connection, storedProcName, parameters);
                command.CommandType = CommandType.StoredProcedure;
                returnReader = command.ExecuteReader(CommandBehavior.CloseConnection);
                command.Dispose();
                return returnReader;
            }
        }

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>SqlDataReader</returns>
        public List<T> RunProcedureToList<T>(string storedProcName, Func<IDataReader, T> reader, DbParameter[] parameters)
        {
            if (reader == null)
            {
                throw new ArgumentNullException("数据读取器是空的!");
            }

            List<T> list = new List<T>();
            using (DbConnection connection = GetDbConnection())
            {
                DbDataReader returnReader;
                connection.Open();
                DbCommand command = BuildQueryCommand(connection, storedProcName, parameters);
                command.CommandType = CommandType.StoredProcedure;
                returnReader = command.ExecuteReader(CommandBehavior.CloseConnection);
                command.Dispose();
                while (returnReader.Read())
                    list.Add(reader(returnReader));
                returnReader.Close();
            }
            return list;
        }

        protected void PrepareCommand(DbCommand cmd, DbConnection conn, DbTransaction trans, string cmdText, DbParameter[] cmdParms)
        {
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (trans != null)
                cmd.Transaction = trans;
            cmd.CommandType = CommandType.Text;
            SetParameters(cmd, cmdParms);
        }

        private DbCommand BuildQueryCommand(DbConnection connection, string storedProcName, DbParameter[] parameters)
        {
            DbCommand command = connection.CreateCommand();
            command.CommandText = storedProcName;
            command.CommandType = CommandType.StoredProcedure;
            SetParameters(command, parameters);
            return command;
        }

        private void SetParameters(DbCommand command, DbParameter[] cmdParms)
        {
            if (cmdParms != null)
            {
                foreach (var parameter in cmdParms)
                {
                    if (
                        (parameter.Direction == ParameterDirection.InputOutput
                        ||
                        parameter.Direction == ParameterDirection.Input)
                        &&
                        (parameter.Value == null))
                    {
                        parameter.Value = DBNull.Value;
                    }

                    command.Parameters.Add(parameter);
                }
            }
        }

        /// <summary>
        /// 组装分页sql
        /// </summary>
        /// <param name="sql">基础sql</param>
        /// <param name="where">条件</param>
        /// <param name="order">排序</param>
        /// <returns></returns>
        private string ListPageSql(string sql, string where, string order)
        {
            if (!where.IsNullOrEmpty())
            {
                sql = sql + " WHERE " + where;
            }

            if (!order.IsNullOrEmpty())
            {
                sql = sql + " " + order;
            }

            return sql;
        }

        /// <summary>
        /// 分页
        /// </summary>
        /// <param name="offset">偏移</param>
        /// <param name="size">每页显示数据尺寸</param>
        /// <returns></returns>
        /// <ArgumentNullException cref="ArgumentNullException"></ArgumentNullException>
        public string Limit(long offset, long size)
        {
            if (offset == -1)
            {
                if (_dataBase.DbType != DbBaseType.SqlServer)
                {
                    return "LIMIT " + size;
                }
            }
            else
            {
                if (_dataBase.DbType == DbBaseType.MySql)
                {
                    return string.Format("LIMIT {0},{1}", offset, size);
                }

                if (_dataBase.DbType == DbBaseType.PostgreSql || _dataBase.DbType == DbBaseType.Sqlite)
                {
                    return string.Format(" LIMIT {0} OFFSET {1}", size, offset);
                }
            }

            throw new ArgumentNullException("暂时不支持其它分页语法");
        }

        /// <summary>
        /// 分页
        /// </summary>
        /// <param name="size">每页显示数据尺寸</param>
        /// <returns></returns>
        public string Limit(long size)
        {
            return Limit(-1L, size);
        }

        /// <summary>
        ///  重命名表名,字段名
        /// </summary>
        /// <param name="name">表名字段名</param>
        /// <returns></returns>
        public string ReName(string name)
        {
            if (name.IsNullOrEmpty())
            {
                return name;
            }

            name = name.Trim();
            //if (_dataBase.DbType == DbBaseType.PostgreSql)
            //{
            //    return "\"" + name + "\"";
            //}

            return "\"" + name + "\"";

            //return name;
        }
    }

    //
    // 摘要:
    //     Execute执行结果
    public class ExecuteResult
    {
        //
        // 摘要:
        //     状态
        public bool Success { get; set; }

        //
        // 摘要:
        //     返回最后添加更改的id
        public long LastId { get; set; }
    }
}